﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ESGRatingsImport.CacheServer;
using System.Runtime.Serialization.Formatters.Binary;
using SpreadsheetGear;

namespace ESGRatingsImport
{
    class Program // NEEDS TO BE EXECUTED ON THE SERVER HOSTING DSRSystemDW2 and ShareDimension.DSRSystemReporting.CacheServer
    {
        private enum IndicatorCategory
        {
            Preparedness = 1, 
            Disclosure = 2, 
            QuantitativePerformance = 3, 
            QualitativePerformance = 4
        }

        static void Main(string[] args)
        {
            const int startRowIndex = 1;
            const int ciqidIndex = 0;
            const int totalScoreIndex = ciqidIndex + 1;
            const int environmentScoreIndex = totalScoreIndex + 1;
            const int socialScoreIndex = environmentScoreIndex + 1;
            const int governanceScoreIndex = socialScoreIndex + 1;
            const int preparednessScoreIndex = governanceScoreIndex + 1;
            const int disclosureScoreIndex = preparednessScoreIndex + 1;
            const int qualitativeScoreIndex = disclosureScoreIndex + 1;
            const int quantitativeScoreIndex = qualitativeScoreIndex + 1;

            var fullyResearchedCompanies = new Dictionary<Guid, string>();
            var indicators = new Dictionary<string, IndicatorCategory>();

            Console.Write("Press a key to start the ESG ratings gathering process...");
            Console.ReadKey();
            Console.Clear();

            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DataWarehouse"].ConnectionString))
            {                
                conn.Open();

                Console.Write("Retrieving fully researched companies from warehouse... ");

                var cmd = new SqlCommand("select CompanyId, CIQID from DimCompany where IsControversyResearchOnly = 0", conn);
                var companiesRetriever = cmd.ExecuteReader();
                    
                while (companiesRetriever.Read())
                {
                    fullyResearchedCompanies.Add((Guid)companiesRetriever["CompanyId"], companiesRetriever["CIQID"] as string);
                }

                Console.WriteLine(fullyResearchedCompanies.Count + " received.");
                companiesRetriever.Close();

                Console.Write("Retrieving indicators with their category from warehouse... ");

                cmd.CommandText = "select IndicatorNumber, IndicatorCategorySortKey from DimIndicator";
                var indicatorsRetriever = cmd.ExecuteReader();

                while (indicatorsRetriever.Read())
                {
                    indicators.Add(indicatorsRetriever["IndicatorNumber"] as string, (IndicatorCategory)indicatorsRetriever["IndicatorCategorySortKey"]);
                }

                Console.WriteLine(indicators.Count + " received.");
                indicatorsRetriever.Close();
            }

            Console.Write("Retrieving fully researched companies scores from cache... ");

            var client = new CacheServer.DSRSystemReportExecutionClient("NetNamedPipeBinding_IDSRSystemReportExecution");

            var companiesRatingsByPeerGroup = 
                client.GetCompanyRatingsByPeerGroups(fullyResearchedCompanies.Keys.ToArray(), new Guid[0], Guid.Empty,
                                                        PeerGroupType.Public, Guid.Empty, 100, TopInClassType.Best, false);

            var companiesRatings =
                new BinaryFormatter().Deserialize(new MemoryStream(companiesRatingsByPeerGroup)) as ShareDimension.DSRSystemReporting.QueryResults.ScoreQueryResult;

            Console.WriteLine(companiesRatings.WeightMatricesScores.Count + " weight matrices scores received.");

            var esgRatingsWorksheet = Factory.GetWorkbookSet().Workbooks.Open("Rating scores import template.xlsx").Worksheets[0];

            for (var c = 0; c < companiesRatings.WeightMatricesScores.Count; c++)
            {
                var companyScores = companiesRatings.WeightMatricesScores[c];
                var rowIndex = startRowIndex + c;

                esgRatingsWorksheet.Cells[rowIndex, ciqidIndex].Value = fullyResearchedCompanies[companyScores.CompanyID];
                esgRatingsWorksheet.Cells[rowIndex, totalScoreIndex].Value = companyScores.Score;
                esgRatingsWorksheet.Cells[rowIndex, environmentScoreIndex].Value = companyScores.Scores[2];// natural order in cache is Governance Social Environment Products
                esgRatingsWorksheet.Cells[rowIndex, socialScoreIndex].Value = companyScores.Scores[1];
                esgRatingsWorksheet.Cells[rowIndex, governanceScoreIndex].Value = companyScores.Scores[0];
                esgRatingsWorksheet.Cells[rowIndex, preparednessScoreIndex].Value =
                    companyScores.WeightedScores.Where(
                        (ws, idx) => indicators.ContainsKey(companiesRatings.ColumnNames[idx]) &&
                                     indicators[companiesRatings.ColumnNames[idx]] == IndicatorCategory.Preparedness &&
                                     ws >= 0)
                        .Sum();
                esgRatingsWorksheet.Cells[rowIndex, disclosureScoreIndex].Value =
                    companyScores.WeightedScores.Where(
                        (ws, idx) => indicators.ContainsKey(companiesRatings.ColumnNames[idx]) &&
                                     indicators[companiesRatings.ColumnNames[idx]] == IndicatorCategory.Disclosure &&
                                     ws >= 0)
                        .Sum();
                esgRatingsWorksheet.Cells[rowIndex, qualitativeScoreIndex].Value =
                    companyScores.WeightedScores.Where(
                        (ws, idx) => indicators.ContainsKey(companiesRatings.ColumnNames[idx]) &&
                                     indicators[companiesRatings.ColumnNames[idx]] == IndicatorCategory.QualitativePerformance &&
                                     ws >= 0)
                        .Sum();
                esgRatingsWorksheet.Cells[rowIndex, quantitativeScoreIndex].Value =
                    companyScores.WeightedScores.Where(
                        (ws, idx) => indicators.ContainsKey(companiesRatings.ColumnNames[idx]) &&
                                     indicators[companiesRatings.ColumnNames[idx]] == IndicatorCategory.QuantitativePerformance &&
                                     ws >= 0)
                        .Sum();
            }

            var importDataFile = string.Format("ESG ratings retrieved on {0:yyyy-MM-dd_HH-mm-ss-fff}.xlsx", DateTime.Now);
            esgRatingsWorksheet.Workbook.SaveAs(importDataFile, FileFormat.OpenXMLWorkbook);

            Console.WriteLine(Environment.NewLine + "ESG ratings import data successfully retrieved and saved in \"" + importDataFile + "\".");

            Console.Write(Environment.NewLine + "Presss any key to close to console...");
            Console.ReadKey();
        }
    }
}
